﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Collections;

namespace Acsys.Entity
{
    public class CreditTransaction : IEntity
    {
        public int ID = 0;
        public string CODE = "D00000";
        public string DESCRIPTION = "";
        public CreditType CREDIT_TYPE = null;
        public DateTime TRANSACTION_DATE = DateTime.Today;
        public Currency CURRENCY = null;
        public Vessel VESSEL = null;
        public TermOfPayment TERM_OF_PAYMENT = null;
        public DateTime ESTIMATED_DATE = DateTime.Today;
        public PaymentStatus PAYMENTSTATUS = PaymentStatus.Open;
        public IList PAYMENT_LIST = new ArrayList();
        public double OUTSTANDING_AMOUNT = 0d;
        public double TOTAL_PAYMENT_AMOUNT = 0d;
        public double AMOUNT = 0d;

        public CreditTransaction()
        {
        }
        public CreditTransaction(int id)
        {
            ID = id;
        }
        public CreditTransaction(int id, string code)
        {
            ID = id;
            CODE = code;
        }
        public IEntity Get(MySqlDataReader aReader)
        {
            CreditTransaction crtr = null;
            while (aReader.Read())
            {
                crtr = new CreditTransaction();
                crtr.ID = Convert.ToInt32(aReader[0]);
                crtr.CODE = aReader[1].ToString();
                crtr.DESCRIPTION = aReader[2].ToString();
                crtr.CREDIT_TYPE= new CreditType(Convert.ToInt32(aReader[3]));
                crtr.TRANSACTION_DATE = Convert.ToDateTime(aReader[4]);
                crtr.CURRENCY = new Currency(Convert.ToInt32(aReader[5]));
                crtr.AMOUNT = Convert.ToDouble(aReader[6]);
                crtr.VESSEL = new Vessel(Convert.ToInt32(aReader[7]));
                crtr.TERM_OF_PAYMENT = new TermOfPayment(Convert.ToInt32(aReader[8]));
                crtr.ESTIMATED_DATE = Convert.ToDateTime(aReader[9]);
                crtr.PAYMENTSTATUS = (PaymentStatus)Enum.Parse(typeof(PaymentStatus), aReader[10].ToString());
                crtr.TOTAL_PAYMENT_AMOUNT = Convert.ToDouble(aReader[11]);
            }
            aReader.Close();
            return crtr;
        }
        public string GetInsertSQL()
        {
            return String.Format(@"insert into table_credittransaction 
                (crtr_code,crtr_description,crty_id,crtr_date,ccy_id,crtr_amount,ves_id,top_id,crtr_estdate,
                crtr_paymentstatus,crtr_totalpaidamount) 
                VALUES ('{0}','{1}','{2}','{3}',{4},{5},{6},{7},'{8}','{9}',{10})",
              CODE, 
              DESCRIPTION, 
              CREDIT_TYPE.ID, 
              TRANSACTION_DATE.ToString("yyyy/MM/dd"),
              CURRENCY.ID,
              AMOUNT,
              VESSEL==null?0:VESSEL.ID, 
              TERM_OF_PAYMENT == null ? 0 : TERM_OF_PAYMENT.ID,
                ESTIMATED_DATE.ToString("yyyy/MM/dd"),
                PAYMENTSTATUS.ToString(), TOTAL_PAYMENT_AMOUNT);
        }
        public string GetDeleteSQL()
        {
            return "delete from table_credittransaction where crtr_id = " + ID;
        }
        public string GetUpdateSQL()
        {
            return String.Format(@"update table_credittransaction set 
                crtr_code = '{0}', 
                crtr_description='{1}',
                crty_id='{2}',
                crtr_date='{3}',
                ccy_id='{4}',
                crtr_amount='{5}',
                ves_id = '{6}',
                top_id = '{7}',
                crtr_estdate = '{8}',
                crtr_paymentstatus = '{9}',
                crtr_totalpaidamount = {10}
                where crtr_id = {11}",
               CODE, 
               DESCRIPTION, 
               CREDIT_TYPE.ID, 
               TRANSACTION_DATE.ToString("yyyy/MM/dd"), 
               CURRENCY.ID,
               AMOUNT,
               VESSEL==null?0:VESSEL.ID,
                TERM_OF_PAYMENT == null ? 0 : TERM_OF_PAYMENT.ID,
                ESTIMATED_DATE.ToString("yyyy/MM/dd"),
                PAYMENTSTATUS.ToString(),
                TOTAL_PAYMENT_AMOUNT,
                ID);
        }
        public string GetByIDSQL(int ID)
        {
            return String.Format("select * from table_credittransaction where crtr_id = {0}", ID);
        }
        public string GetByCodeSQL(string code)
        {
            return String.Format("select * from table_credittransaction where crtr_code = '{0}'", code);
        }
        public string GetByCodeLikeSQL(string text)
        {
            return String.Format("select * from table_credittransaction where crtr_code like '%{0}%'", text);
        }
        public string GetByNameLikeSQL(string text)
        {
            return String.Format("select * from table_credittransaction where crtr_description like '%{0}%'", text);
        }
        public string GetAllSQL()
        {
            return String.Format("select * from table_credittransaction");
        }
        public string GetAllSQL(DateTime fromDate, DateTime toDate, bool allcrType, int crTypeID,
            bool allCcy, int ccyID, bool allVessel, int vesselID, bool noneVessel, string status, bool allStatus)
        {
            string alldrTypeFilter = allcrType ? "" : " and t.crty_id = " + crTypeID.ToString();
            string allCcyFilter = allCcy ? "" : " and t.ccy_id = " + ccyID.ToString();
            string allVesselFilter = allVessel ? "" : noneVessel ? " and t.ves_id = 0  " : " and t.ves_id = " + vesselID.ToString();
            string allStasusFilter = allStatus ? "" : " and t.crtr_paymentstatus = '" + status + "'";
            //string sql = @"SELECT * FROM table_credittransaction t, table_transactiontype crty,table_currency ccy, table_vessel v where t.crtr_date between '" + fromDate.ToString("yyyy/MM/dd") + @"'
//and '" + toDate.ToString("yyyy/MM/dd") + "' and t.crty_id = crty.crty_id and ccy.ccy_id = t.ccy_id and t.ves_id = v.ves_id " + alldrTypeFilter + allCcyFilter + allVesselFilter;
            string sql = @"SELECT * FROM table_credittransaction t
                            inner join table_transactiontype crty on t.crty_id = crty.crty_id
                            inner join table_currency ccy on ccy.ccy_id = t.ccy_id
                            left join table_vessel v on t.ves_id = v.ves_id
                            where t.crtr_date between '" + fromDate.ToString("yyyy/MM/dd") + @"'
                            and '" + toDate.ToString("yyyy/MM/dd") + "'" + alldrTypeFilter + allCcyFilter +
                                   allVesselFilter + allStasusFilter + " order by t.crtr_date"; 
            return sql;
        }
        public string GetAllSQL(int id)
        {
            string sql = @"SELECT * FROM table_credittransaction t, table_transactiontype crty,table_currency ccy where t.crty_id = crty.crty_id and ccy.ccy_id = t.ccy_id and t.crtr_id = " + id.ToString();
            return sql;
        }
        public IList GetAll(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                CreditTransaction crtr = new CreditTransaction();
                crtr.ID = Convert.ToInt32(aReader[0]);
                crtr.CODE = aReader[1].ToString();
                crtr.DESCRIPTION = aReader[2].ToString();
                crtr.CREDIT_TYPE = new CreditType(Convert.ToInt32(aReader[3]));
                crtr.TRANSACTION_DATE = Convert.ToDateTime(aReader[4]);
                crtr.CURRENCY = new Currency(Convert.ToInt32(aReader[5]));
                crtr.AMOUNT = Convert.ToDouble(aReader[6]);
                crtr.VESSEL = new Vessel(Convert.ToInt32(aReader[7]));
                crtr.TERM_OF_PAYMENT = new TermOfPayment(Convert.ToInt32(aReader[8]));
                crtr.ESTIMATED_DATE = Convert.ToDateTime(aReader[9]);
                crtr.PAYMENTSTATUS = (PaymentStatus)Enum.Parse(typeof(PaymentStatus), aReader[10].ToString());
                crtr.TOTAL_PAYMENT_AMOUNT = Convert.ToDouble(aReader[11]);
                result.Add(crtr);
            }
            aReader.Close();
            return result;
        }
        public int GetID()
        {
            return ID;
        }
        public void SetID(int id)
        {
            ID = id;
        }
        public override string ToString()
        {
            return CODE + " - " + DESCRIPTION;
        }
        public string GetCode()
        {
            return CODE;
        }
        public void SetCode(string code)
        {
            CODE = code;
        }
        public string GetMaximumIDSQL()
        {
            return String.Format("SELECT max(crtr_id) from table_credittransaction");
        }
        public string GetSearchSQL(string search)
        {
            return String.Format(@"SELECT t.* FROM table_credittransaction t
                INNER JOIN table_transactiontype dt on dt.crty_id = t.crty_id
                where concat(t.crtr_code, t.crtr_description,
                t.crtr_paymentstatus,dt.crty_code,dt.crty_name) like '%{0}%'", search);
        }
    }
}
